import pandas as pd
import numpy as np
from pathlib import Path
import re

def clean_text(text):
    """清理文本，去除特殊字符和空格"""
    if pd.isna(text):
        return None
    # 将文本转换为字符串，并处理浮点数的情况
    try:
        if isinstance(text, float):
            text = str(int(text))
        else:
            text = str(text)
    except:
        return None
    # 去除特殊字符和空格
    cleaned = re.sub(r'[^\w\u4e00-\u9fff]', '', text)
    return cleaned

def match_job_level(job_level_name, standard_job_level):
    """
    匹配职务级别
    
    Args:
        job_level_name: 实际的报销级别名称
        standard_job_level: 标准中的职务级别（可能包含多个级别，以、分隔）
    
    Returns:
        bool: 是否匹配
    """
    if pd.isna(job_level_name) or pd.isna(standard_job_level):
        return False
        
    job_level_name = clean_text(job_level_name)
    if not job_level_name:
        return False
    
    # 将标准职务级别按、分割成列表
    standard_levels = [clean_text(level) for level in standard_job_level.split('、')]
    standard_levels = [level for level in standard_levels if level]  # 移除空值
    
    # 检查报销级别名称是否完全匹配标准级别中的任何一个
    return any(job_level_name == level for level in standard_levels)

def city_in_standard_cities(city_name, standard_cities):
    """
    检查城市是否在标准城市列表中
    
    Args:
        city_name: 要检查的城市名称
        standard_cities: 标准中的城市列表（可能是以、分隔的字符串）
    
    Returns:
        bool: 是否匹配
    """
    if pd.isna(standard_cities) or standard_cities == '其它地区':
        return False
        
    city_name = clean_text(city_name)
    if not city_name:
        return False
    
    # 将标准城市按、分割，并清理每个城市名称
    standard_city_list = [clean_text(city) for city in standard_cities.split('、')]
    standard_city_list = [city for city in standard_city_list if city]
    
    # 检查是否匹配
    return any(city_name == city for city in standard_city_list)

def get_reimbursement_standard(standards_df, city_name, job_level_name, invoice_date):
    """
    获取报销标准
    
    Args:
        standards_df: 报销标准DataFrame
        city_name: 城市名称
        job_level_name: 报销级别名称
        invoice_date: 开票日期
    
    Returns:
        float: 报销标准金额
        str: 匹配类型（'特殊城市'/'其它地区'）
        str: 匹配的职务级别
        str: 匹配的城市组
        str: 匹配说明
    """
    if pd.isna(city_name):
        return None, None, None, None, "城市名称为空"
    if pd.isna(job_level_name):
        return None, None, None, None, "职务级别为空"
    if pd.isna(invoice_date):
        return None, None, None, None, "开票日期为空"
        
    # 清理城市名称
    cleaned_city = clean_text(city_name)
    if not cleaned_city:
        return None, None, None, None, f"城市名称 '{city_name}' 清理后为空"
    
    # 首先根据日期筛选适用的标准行
    applicable_standards = standards_df[
        (standards_df['标准开始日期'] <= invoice_date) &
        (standards_df['标准结束日期'] >= invoice_date)
    ]
    
    if applicable_standards.empty:
        return None, None, None, None, f"开票日期 {invoice_date.strftime('%Y-%m-%d')} 不在任何标准的有效期内"
    
    # 记录是否找到匹配的职务级别
    found_matching_job_level = False
    matched_standards = []
    
    # 遍历每个标准行，先匹配职务级别
    for _, standard_row in applicable_standards.iterrows():
        if match_job_level(job_level_name, standard_row['职务级别']):
            found_matching_job_level = True
            matched_standards.append(standard_row)
    
    if not found_matching_job_level:
        # 如果没有找到匹配的职务级别，返回所有可用的职务级别供参考
        available_levels = applicable_standards['职务级别'].unique()
        return None, None, None, None, f"职务级别 '{job_level_name}' 未找到匹配的标准。可用的职务级别包括：{', '.join(available_levels)}"
    
    # 在匹配的职务级别中查找城市匹配
    for standard_row in matched_standards:
        if city_in_standard_cities(cleaned_city, standard_row['城市名称']):
            return (
                float(standard_row['费用标准']), 
                '特殊城市', 
                standard_row['职务级别'],
                standard_row['城市名称'],
                f"匹配到特殊城市组：{standard_row['城市名称']}"
            )
    
    # 如果没有匹配到特殊城市，查找对应职务级别的其它地区标准
    other_area_standards = [row for row in matched_standards if row['城市名称'] == '其它地区']
    
    if other_area_standards:
        standard_row = other_area_standards[0]  # 取第一个其它地区标准
        return (
            float(standard_row['费用标准']), 
            '其它地区', 
            standard_row['职务级别'],
            '其它地区',
            f"城市 '{city_name}' 未在特殊城市组中找到，使用其它地区标准"
        )
    
    return None, None, None, None, f"职务级别 '{job_level_name}' 在当前日期范围内没有对应的其它地区标准"

def main(once = False):
    """主函数：分析住宿费标准"""
    try:
        # 设置数据文件路径
        data_dir = Path("data")
        alldata_dir = data_dir / "alldata"
        alldata_dir.mkdir(parents=True, exist_ok=True)
        
        # 读取源数据
        print("正在读取源数据...")
        
        # 读取差旅费票据信息和差旅报销行程明细
        tickets_df = pd.read_excel(data_dir / "差旅费票据信息.xlsx")
        travel_df = pd.read_excel(data_dir / "差旅报销行程明细(商旅).xlsx")
        standards_df = pd.read_excel(data_dir / "报销等级标准.xlsx")
        
        # 打印数据形状
        print(f"票据信息记录数: {len(tickets_df)}")
        print(f"行程明细记录数: {len(travel_df)}")
        print(f"报销标准记录数: {len(standards_df)}")
        
        # 清理列名中的空格
        tickets_df.columns = tickets_df.columns.str.strip()
        travel_df.columns = travel_df.columns.str.strip()
        standards_df.columns = standards_df.columns.str.strip()
        
        # 打印关键列名，用于调试
        print("\n票据信息关键列:")
        print(tickets_df.columns.tolist())
        print("\n行程明细关键列:")
        print(travel_df.columns.tolist())
        
        # 过滤掉发票号码为空的行
        tickets_df = tickets_df.dropna(subset=['发票号码'])
        # 过滤掉开票日期为空的行
        tickets_df = tickets_df.dropna(subset=['开票日期'])
        
        # 转换日期列用于计算，但保留原始格式
        standards_df['标准开始日期'] = pd.to_datetime(standards_df['标准开始日期'])
        standards_df['标准结束日期'] = pd.to_datetime(standards_df['标准结束日期'])
        tickets_df['开票日期_计算用'] = pd.to_datetime(tickets_df['开票日期'])
        
        print(f"\n过滤后的票据记录数: {len(tickets_df)}")
        
        # 创建结果列表和未匹配记录列表
        result_rows = []
        unmatched_rows = []
        
        # 处理每条票据记录
        total_records = len(tickets_df)
        print(f"\n开始处理 {total_records} 条记录...")
        
        for _, ticket in tickets_df.iterrows():
            try:
                # 获取对应的行程记录
                matching_travel = travel_df[
                    (travel_df['唯一标识'] == ticket['唯一标识'])
                ]
                
                # 如果找不到匹配的唯一标识
                if matching_travel.empty:
                    unmatched_rows.append({
                        '唯一标识': ticket['唯一标识'],
                        '发票人员名称': ticket['发票人员名称'],
                        '未匹配原因': '未找到对应的唯一标识'
                    })
                    continue
                
                # 在匹配的唯一标识中查找对应的人员名称
                matching_travel = matching_travel[
                    matching_travel['人员名称'] == ticket['发票人员名称']
                ]
                
                # 如果找不到匹配的人员名称
                if matching_travel.empty:
                    unmatched_rows.append({
                        '唯一标识': ticket['唯一标识'],
                        '发票人员名称': ticket['发票人员名称'],
                        '未匹配原因': '在对应的唯一标识中未找到匹配的人员名称'
                    })
                    continue
                
                # 使用第一条匹配的记录
                travel_record = matching_travel.iloc[0]
                
                if pd.isna(ticket['城市名称']):
                    unmatched_rows.append({
                        '唯一标识': ticket['唯一标识'],
                        '发票人员名称': ticket['发票人员名称'],
                        '未匹配原因': '城市名称为空'
                    })
                    continue

                # 检查住宿天数
                if pd.isna(ticket['住宿天数']) or ticket['住宿天数'] <= 0:
                    unmatched_rows.append({
                        '唯一标识': ticket['唯一标识'],
                        '发票人员名称': ticket['发票人员名称'],
                        '城市名称': ticket['城市名称'],
                        '未匹配原因': '住宿天数无效'
                    })
                    continue

                # 获取报销标准
                standard_amount, standard_type, matched_job_level, matched_cities, match_desc = get_reimbursement_standard(
                    standards_df,
                    ticket['城市名称'],
                    travel_record.get('报销级别名称', ''),
                    ticket['开票日期_计算用']
                )
                
                # 如果没有找到匹配的标准，记录到未匹配列表
                if standard_amount is None:
                    unmatched_rows.append({
                        '唯一标识': ticket['唯一标识'],
                        '发票人员名称': ticket['发票人员名称'],
                        '城市名称': ticket['城市名称'],
                        '报销级别名称': travel_record.get('报销级别名称', ''),
                        '开票日期': ticket['开票日期'],
                        '未匹配原因': match_desc
                    })
                    continue
                
                # 获取金额和计算每日报销单价
                invoice_amount = float(ticket['发票金额']) if pd.notna(ticket['发票金额']) else 0
                stay_days = float(ticket['住宿天数'])
                daily_rate = round(invoice_amount / stay_days, 2)
                
                # 计算是否超标（使用每日报销单价与标准比较）
                is_over_standard = '是' if daily_rate > standard_amount else '否'
                over_amount = round((daily_rate - standard_amount) * stay_days, 2) if is_over_standard == '是' else 0
                
                # 只记录超标的结果
                if is_over_standard == '是':
                    result_row = {
                        # 原始票据信息
                        '唯一标识': ticket['唯一标识'],
                        '年度': pd.to_datetime(ticket['开票日期_计算用']).year,
                        'fmis_单位编码': ticket.get('fmis_单位编码', ''),
                        'fmis_单位名称': ticket.get('fmis_单位名称', ''),
                        '单据编号': ticket.get('单据编号', ''),
                        '分录编号': ticket.get('分录编号', ''),
                        '发票人员名称': ticket['发票人员名称'],
                        '城市名称': ticket['城市名称'],
                        '宾馆': ticket.get('宾馆', ''),
                        '住宿天数': stay_days,
                        '发票类型': ticket.get('发票类型', ''),
                        '发票号码': ticket['发票号码'],
                        '开票日期': ticket['开票日期'],
                        '发票金额': invoice_amount,
                        '每日住宿费': daily_rate,
                        '发票税额': float(ticket['发票税额']) if pd.notna(ticket['发票税额']) else 0,
                        '发票含税金额': float(ticket['发票含税金额']) if pd.notna(ticket['发票含税金额']) else 0,
                        
                        # 报销级别信息
                        '报销级别': travel_record.get('报销级别', ''),
                        '报销级别名称': travel_record.get('报销级别名称', ''),
                        '申请事由': travel_record.get('申请事由', ''),  # 添加申请事由字段
                        
                        # 匹配结果
                        '匹配的职务级别': matched_job_level,
                        '匹配的城市组': matched_cities,
                        '适用报销标准': standard_amount,
                        '标准类型': standard_type,
                        '匹配说明': match_desc,
                        '是否超标': is_over_standard,
                        '日超标金额': round(daily_rate - standard_amount, 2),
                        '总超标金额': over_amount
                    }
                    result_rows.append(result_row)
                    
            except Exception as e:
                print(f"处理记录时出错 (唯一标识: {ticket.get('唯一标识', '未知')}): {str(e)}")
                unmatched_rows.append({
                    '唯一标识': ticket.get('唯一标识', '未知'),
                    '发票人员名称': ticket.get('发票人员名称', '未知'),
                    '未匹配原因': f'处理出错: {str(e)}'
                })
                continue
        
        if result_rows:
            # 创建结果DataFrame
            result_df = pd.DataFrame(result_rows)
            
            # 筛选培训相关的记录
            training_df = result_df
            if '申请事由' in result_df.columns:
                training_mask = result_df['申请事由'].fillna('').astype(str).str.contains('培训')
                training_df = result_df[training_mask].copy()
                print(f"\n总超标记录数: {len(result_df)}")
                print(f"其中培训相关记录数: {len(training_df)}")
                print(f"非培训记录数: {len(result_df) - len(training_df)}")
            else:
                print("\n警告：数据中没有'申请事由'列，无法筛选培训记录")

            if once:
                return training_df

            # 设置Excel输出选项，不使用科学计数法
            with pd.ExcelWriter(alldata_dir / "住宿费超标分析结果(培训申请).xlsx", engine='openpyxl') as writer:
                training_df.to_excel(writer, index=False)
            
            print(f"\n分析完成！已保存 {len(training_df)} 条培训相关的超标记录")
            
            # 如果有未匹配的记录，保存到单独的文件
            if unmatched_rows:
                unmatched_df = pd.DataFrame(unmatched_rows)
                unmatched_file = alldata_dir / "未匹配记录.xlsx"
                unmatched_df.to_excel(unmatched_file, index=False)
                print(f"\n另有 {len(unmatched_df)} 条未匹配记录，已保存到 '未匹配记录.xlsx'")
                
                # 打印未匹配原因统计
                print("\n未匹配原因统计:")
                reason_stats = unmatched_df['未匹配原因'].value_counts()
                print(reason_stats.to_string())
            
            # 打印超标统计信息
            print("\n培训记录超标情况统计:")
            level_stats = training_df.groupby(['报销级别名称', '匹配的职务级别', '匹配的城市组', '标准类型', '适用报销标准']).agg({
                '发票号码': 'count',
                '发票金额': ['sum', 'mean'],
                '每日住宿费': ['mean', 'max'],
                '总超标金额': 'sum'
            }).round(2)
            level_stats.columns = ['数量', '总金额', '平均金额', '平均每日住宿费', '最高每日住宿费', '总超标金额']
            print(level_stats.to_string())
            
        else:
            print("未发现超标记录。")
            if unmatched_rows:
                unmatched_df = pd.DataFrame(unmatched_rows)
                unmatched_file = alldata_dir / "未匹配记录.xlsx"
                unmatched_df.to_excel(unmatched_file, index=False)
                print(f"\n发现 {len(unmatched_df)} 条未匹配记录，已保存到 '未匹配记录.xlsx'")
                
                # 打印未匹配原因统计
                print("\n未匹配原因统计:")
                reason_stats = unmatched_df['未匹配原因'].value_counts()
                print(reason_stats.to_string())
        
        return True
        
    except Exception as e:
        print(f"处理过程中出现错误: {str(e)}")
        return False

if __name__ == "__main__":
    main()